﻿CREATE PROCEDURE [dbo].[sp_Get_RequesterCode]
	@AccountID varchar(4), 
	@PeroidMonth varchar(2),
	@PeroidYear varchar(4)
AS
		select z.Zip, zi.Territory, zi.Market,
		(case when oz.Type in (0, 1) then zi.ActiveLeadCount else 0 end) as ActiveLeadCount,
		(case when oz.Type in (0, 1) then zi.LapsedLeadCount else 0 end) as LapsedLeadCount,
		(case when oz.Type in (0, 2) then zi.BusinesLeadCount else 0 end) as BusinesLeadCount,
		(case when oz.Type in (0, 1) then zi.NeighborLeadCount else 0 end) as NeighborLeadCount,
		zi.Latitude, zi.Longitude, zi.AgentName, zi.City, zi.MdNumber, zi.Division
		from dbo.Orders o
		inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
		inner join dbo.Zips z on z.ZipID = oz.ZipID
		inner join dbo.ZipInfo zi on zi.ZipID = z.ZipID
		where AccountID = @AccountID and PeroidMonth = @PeroidMonth 
		and PeroidYear = @PeroidYear and IsPurchased = 1